package com.keyware.utils;

import java.io.File;
import java.io.UnsupportedEncodingException;
import java.net.URL;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.LinkedList;
import java.util.Properties;

import org.springframework.util.StringUtils;

import com.aspose.words.Document;
import com.aspose.words.DocumentBuilder;
import com.aspose.words.License;
import com.aspose.words.SaveFormat;
public class DbInfoUtil {
	 /** 
    * 根据数据库的连接参数，获取指定表的基本信息：字段名、字段类型、字段注释 
    * @param driver 数据库连接驱动 
    * @param url 数据库连接url 
    * @param user  数据库登陆用户名 
    * @param pwd 数据库登陆密码 
    * @param table 表名 
    * @return Map集合 
    */  
   public static LinkedHashSet<String> getTableInfo(Connection conn){  
       LinkedHashSet<String> linkedHashSet = new LinkedHashSet<String>();
       DatabaseMetaData dbmd = null;  
       try {
			dbmd = conn.getMetaData();
			//ResultSet resultSet = dbmd.getTables(null, "%", table, new String[] { "TABLE" });
			ResultSet resultSet = dbmd.getTables(conn.getCatalog(), getSchema(conn),"%",new String[]{"TABLE"});
			while (resultSet.next()) {  
				String tableName=resultSet.getString("TABLE_NAME");  
				String remarks=resultSet.getString("REMARKS");
				if(StringUtils.hasText(remarks)){
					linkedHashSet.add(remarks+":"+tableName);
				}else{
					linkedHashSet.add(tableName);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}  
       return linkedHashSet;  
   }

	private static LinkedList<LinkedHashMap<String,String>> getTableColumn(Connection conn, String tableName) throws SQLException, Exception {
		LinkedList<LinkedHashMap<String,String>> linkedList = new LinkedList<LinkedHashMap<String,String>>();
		
		//获取表的主键
		ResultSet primaryKeyResultSet = conn.getMetaData().getPrimaryKeys(conn.getCatalog(),getSchema(conn),tableName);  
		String primaryKeyColumnName = "";
		while(primaryKeyResultSet.next()){  
		    primaryKeyColumnName = primaryKeyResultSet.getString("COLUMN_NAME");  
		}  
		
		ResultSet rs = conn.getMetaData().getColumns(null, getSchema(conn),tableName.toUpperCase(), "%");
		
		while(rs.next()){
			LinkedHashMap<String,String> map = new LinkedHashMap<String,String>();
			map.put("primaryKey", primaryKeyColumnName);
		    //字段名称
		    String columnName = rs.getString("COLUMN_NAME");  
		    map.put("columnName", columnName.toUpperCase());  
		      
		    //数据类型
		    String dbType = rs.getString("TYPE_NAME"); 
		    if(dbType.equals("NUMBER") || dbType.equals("INTEGER")){
		    	dbType = "整型";
		    }else if(dbType.equals("CLOB") || dbType.equals("BLOB")){
		    	dbType = "二进制类型";
		    }else{
		    	dbType = "字符串类型";
		    }
		    map.put("dbType",dbType);
		    
		    //字段长度
		 	int datasize = rs.getInt("COLUMN_SIZE");
		    map.put("datasize", datasize+"");
		    
		    //注释
		    String remarks = rs.getString("REMARKS");  
		   /* if(remarks == null || remarks.equals("")){  
		        remarks = columnName;  
		    }*/
		    map.put("remarks",remarks);  
		    linkedList.add(map);
		}
		return linkedList;
	}  
     
/*    private static String changeDbType(String dbType) {  
       dbType = dbType.toUpperCase();  
       switch(dbType){  
           case "VARCHAR":  
           case "VARCHAR2":  
           case "CHAR":  
               return "1";  
           case "NUMBER":  
           case "DECIMAL":  
               return "4";  
           case "INT":  
           case "SMALLINT":  
           case "INTEGER":  
               return "2";  
           case "BIGINT":  
               return "6";  
           case "DATETIME":  
           case "TIMESTAMP":  
           case "DATE":  
               return "7";  
           default:  
               return "1";  
       }  
   }  
*/  
   //获取连接  
   private static Connection getConnections(String driver,String url,String user,String pwd) throws Exception {  
       Connection conn = null;  
       try {  
           Properties props = new Properties();  
           props.put("remarksReporting", "true");  
           props.put("user", user);  
           props.put("password", pwd);  
           Class.forName(driver);  
           conn = DriverManager.getConnection(url, props);  
       } catch (Exception e) {  
           e.printStackTrace();  
           throw e;  
       }  
       return conn;  
   }  
     
   //其他数据库不需要这个方法 oracle和db2需要  
   private static String getSchema(Connection conn) throws Exception {  
       String schema;  
       schema = conn.getMetaData().getUserName();  
       if ((schema == null) || (schema.length() == 0)) {  
           throw new Exception("ORACLE数据库模式不允许为空");  
       }  
       return schema.toUpperCase().toString();  
 
   }  
 //aspose授权
   public static void setUnlimitedLicense()  {
		URL licenseFileNameURL = DbInfoUtil.class
				.getResource("/com/keyware/kd/word/Aspose.Words.lic");
		String savePath;
		try {
			savePath = java.net.URLDecoder.decode(licenseFileNameURL
					.toString(), "utf-8");
			String licenseFileName = savePath.toString().substring(6);
			if (new File(licenseFileName).exists()) {
				
				License license = new License();
				try {
					license.setLicense(licenseFileName);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
	}
   
   
   public static void main(String[] args) {  
       String driver = "oracle.jdbc.driver.OracleDriver";  
       String url = "jdbc:oracle:thin:@192.168.0.249:1521:orcl";  
       String user = "kdAdmin";  
       String pwd = "123456";
       Connection conn = null;
       //aspose授权验证
       setUnlimitedLicense();
       try {
       	
			conn = getConnections(driver,url,user,pwd);
			LinkedHashSet<String> list = getTableInfo(conn);
			try {
				conn.close();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			//读取样式模板
			Document targetDocument = new Document("E:\\GJB438B编写规范标准样式模板.doc");
			DocumentBuilder targetDocumentBuilder = new DocumentBuilder(targetDocument);
			targetDocument.removeChild(targetDocument.getLastChild());	
			targetDocument.updateFields();
			
			
			targetDocumentBuilder.moveToMergeField("正文");
			targetDocumentBuilder.getPageSetup().setRestartPageNumbering(true);
			targetDocumentBuilder.getPageSetup().setPageStartingNumber(1);
			
			targetDocumentBuilder.getParagraphFormat().setStyle(targetDocument.getStyles().get("标题 1"));
			targetDocumentBuilder.writeln("数据库详细设计");
			
			int i = 0 ;
			for(String tableName:list){
				i++;
				targetDocumentBuilder.getFont().clearFormatting();
				targetDocumentBuilder.getParagraphFormat().clearFormatting();
				
				conn = getConnections(driver,url,user,pwd);
				String selectTableName = tableName;
				if(tableName.indexOf(":")>0){
					String[] split = tableName.split(":");
					if(split.length == 2){
						selectTableName = split[1];
					}
				}
				targetDocumentBuilder.getFont().clearFormatting();
				targetDocumentBuilder.getParagraphFormat().clearFormatting();
				targetDocumentBuilder.getParagraphFormat().setStyle(targetDocument.getStyles().get("标题 2"));
				targetDocumentBuilder.writeln(tableName);
				targetDocumentBuilder.getFont().clearFormatting();
				targetDocumentBuilder.getParagraphFormat().clearFormatting();
				//输出表头
				targetDocumentBuilder.write("表 "+i+" "+tableName);	
				targetDocumentBuilder.startTable();
				targetDocumentBuilder.insertCell();
				targetDocumentBuilder.write("字段名称");	
				targetDocumentBuilder.insertCell();
				targetDocumentBuilder.write("英文名称");
				targetDocumentBuilder.insertCell();
				targetDocumentBuilder.write("类型");
				targetDocumentBuilder.insertCell();
				targetDocumentBuilder.write("主、外键");
				targetDocumentBuilder.insertCell();
				targetDocumentBuilder.write("注释");
				targetDocumentBuilder.endRow();
				LinkedList<LinkedHashMap<String, String>> tableColumn = getTableColumn(conn, selectTableName);
				try {
					conn.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
				targetDocumentBuilder.getParagraphFormat().setStyle(targetDocument.getStyles().get("正文"));
				
				for(LinkedHashMap<String, String> hashMap:tableColumn){
					String columnName = hashMap.get("columnName");
					String remarks = hashMap.get("remarks") == null ? "" : hashMap.get("remarks");
					String dbType = hashMap.get("dbType");
					String primaryKey = hashMap.get("primaryKey");
					targetDocumentBuilder.insertCell();
					targetDocumentBuilder.write(remarks);	
					targetDocumentBuilder.insertCell();
					targetDocumentBuilder.write(columnName);
					targetDocumentBuilder.insertCell();
					targetDocumentBuilder.write(dbType);
					targetDocumentBuilder.insertCell();
					if (primaryKey.equals(columnName)) 
						targetDocumentBuilder.write("主键");
					else
						targetDocumentBuilder.write(" ");
					targetDocumentBuilder.insertCell();
					targetDocumentBuilder.write(remarks);
					targetDocumentBuilder.endRow();
				}
				targetDocumentBuilder.getFont().clearFormatting();
				targetDocumentBuilder.getParagraphFormat().clearFormatting();
				targetDocumentBuilder.moveToDocumentEnd();
			}
			targetDocument.save("E:\\数据库详细说明.docx", SaveFormat.DOCX);			
		} catch (Exception e) {
			try {
				conn.close();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
   } 
}
